MySQL 学习（13）视图和分区表

视图是什么？

在 MySQL 数据库中，视图（View）是一个命名的虚表，它由一个 SQL 查询来定义，可以当做表使用。与持久表（permanent table）不同的是，视图中的数据没有实际的物理存储。

视图的作用

视图在数据库中发挥着重要的作用。视图的主要用途之一是被用做一个抽象装置，特别是对于一些应用程序，程序本身不需要关心基表（base table）的结构，只需要按照视图定义来取数据或更新数据，因此，视图同时在一定程度上起到一个安全层的作用。

MySQL 数据库从 5.0 版本开始支持视图，创建视图的语法如下：

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    VIEW view_name [(column_list)]
    AS select_statement
   [WITH [CASCADED | LOCAL] CHECK OPTION]

1、OR REPLACE：表示替换已有视图

2、ALGORITHM：表示视图选择算法，默认算法是 UNDEFINED（未定义的）：MySQL 自动选择要使用的算法；merge 合并；temptable 临时表

3、select_statement：表示 select 语句

4、[WITH [CASCADED | LOCAL] CHECK OPTION]：表示视图在更新时保证在视图的权限范围之内

cascade 是默认值，表示更新视图的时候，要满足视图和表的相关条件
local 表示更新视图的时候，要满足该视图定义的一个条件即可

推荐使用 WHIT [CASCADED|LOCAL] CHECK OPTION 选项，可以保证数据的安全性

定义视图

基本格式：

create view <视图名称>[(column_list)]
as 
  select语句
  with check option;

例如：在单表上创建视图

create view v_F_players(编号,名字,性别,电话)
as
  select PLAYERNO,NAME,SEX,PHONENO from PLAYERS
  where SEX='F'
  with check option;

例如：在多表上创建视图

create view v_match
as 
  select a.PLAYERNO,a.NAME,MATCHNO,WON,LOST,c.TEAMNO,c.DIVISION
  from 
  PLAYERS a,MATCHES b,TEAMS c
  where a.PLAYERNO=b.PLAYERNO and b.TEAMNO=c.TEAMNO;

虽然视图是基于基表的一个虚拟表，但是用户可以对某些视图进行更新操作，其本质就是通过视图的定义来更新基本表。一般称可以进行更新操作的视图为可更新视图（updatable view）。

视图定义中的 WITH CHECK OPTION 就是针对于可更新的视图的，即更新的值是否需要检查。先看下面的一个例子：

分区表 ⭐

随着 MySQL 越来越流行，MySQL 里面的保存的数据也越来越大。

在日常的工作中，我们经常遇到一张表里面保存了上亿甚至过十亿的记录。这些表里面保存了大量的历史记录。对于这些历史数据的清理是一个非常头疼事情，由于所有的数据都一个普通的表里。所以只能是启用一个或多个带 where 条件的 delete 语句去删除（一般 where 条件是时间）。

这对数据库的造成了很大压力。即使我们把这些删除了，但底层的数据文件并没有变小。面对这类问题，最有效的方法就是在使用分区表。最常见的分区方法就是按照时间进行分区。分区一个最大的优点就是可以非常高效的进行历史数据的清理。

通俗地讲表分区是将一大表，根据条件分割成若干个小表。

MySQL 在创建表的时候可以通过使用 PARTITION BY 子句定义每个分区存放的数据。在执行查询的时候，优化器根据分区定义过滤那些没有我们需要的数据的分区，这样查询就可以无需扫描所有分区，只需要查找包含需要数据的分区即可。

分区的另一个目的是将数据按照一个较粗的粒度分别存放在不同的表中。这样做可以将相关的数据存放在一起，另外，当我们想要一次批量删除整个分区的数据也会变得很方便。

分区操作示例

以 RANGE 分区为例，介绍下分区表相关的操作。

-- 创建分区表
CREATE TABLE `tr` (
  `id` INT, 
  `name` VARCHAR(50), 
  `purchased` DATE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE( YEAR(purchased) ) (
PARTITION p0 VALUES LESS THAN (1990),
PARTITION p1 VALUES LESS THAN (1995),
PARTITION p2 VALUES LESS THAN (2000),
PARTITION p3 VALUES LESS THAN (2005),
PARTITION p4 VALUES LESS THAN (2010),
PARTITION p5 VALUES LESS THAN (2015)
);

-- 插入数据
INSERT INTO `tr` VALUES
    (1, 'desk organiser', '2003-10-15'),
    (2, 'alarm clock', '1997-11-05'),
    (3, 'chair', '2009-03-10'),
    (4, 'bookcase', '1989-01-10'),
    (5, 'exercise bike', '2014-05-09'),
    (6, 'sofa', '1987-06-05'),
    (7, 'espresso maker', '2011-11-22'),
    (8, 'aquarium', '1992-08-04'),
    (9, 'study desk', '2006-09-16'),
    (10, 'lava lamp', '1998-12-25');

创建后可以看到，每个分区都会对应 1 个 ibd 文件。

上面创建语句还是很好理解的，在此分区表中，通过 YEAR 函数取出 DATE 日期中的年份并转化为整型，年份小于 1990 的存储在分区 p0 中，小于 1995 的存储在分区 p1 中，以此类推。

请注意，每个分区的定义顺序是从最低到最高。为了防止插入的数据因找不到相应分区而报错，我们应该及时创建新的分区。下面继续展示关于分区维护的其他操作。

-- 查看某个分区的数据
SELECT * FROM tr PARTITION (p2);
-- +------+-------------+------------+
-- | id   | name        | purchased  |
-- +------+-------------+------------+
-- |    2 | alarm clock | 1997-11-05 |
-- |   10 | lava lamp   | 1998-12-25 |
-- +------+-------------+------------+

-- 增加分区
alter table tr add partition(
  PARTITION p6 VALUES LESS THAN (2020)
);

-- 拆分分区
alter table tr reorganize partition p5 into(
  partition s0 values less than(2012),
  partition s1 values less than(2015)
);

-- 合并分区
alter table tr reorganize partition s0,s1 into ( 
    partition p5 values less than (2015) 
);

-- 清空某分区的数据
alter table tr truncate partition p0;

-- 删除分区
alter table tr drop partition p1;

-- 交换分区
-- 先创建与分区表同样结构的交换表
CREATE TABLE `tr_archive` (
  `id` INT, 
  `name` VARCHAR(50), 
  `purchased` DATE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 执行 exchange 交换分区 
alter table tr exchange PARTITION p2 with table tr_archive;

分区类型

目前 MySQL 支持范围分区（RANGE），列表分区（LIST），哈希分区（HASH）以及 KEY 分区四种。下面我们逐一介绍每种分区：

RANGE 分区

RANGE 分区是实战最常用的一种分区类型，行数据基于属于一个给定的连续区间的列值被放入分区。

但是记住，当插入的数据不在一个分区中定义的值的时候，会抛异常。RANGE 分区主要用于日期列的分区，比如交易表啊，销售表啊等。可以根据年月来存放数据。

如果你分区走的唯一索引中 date 类型的数据，那么注意了，优化器只能对 YEAR(),TO_DAYS(),TO_SECONDS(),UNIX_TIMESTAMP() 这类函数进行优化选择。

实战中可以用 int 类型，那么只用存 yyyyMM 就好了。也不用关心函数了。

-- 注意，这整个是一条语句
CREATE TABLE `Order`
(
    `id`            INT        NOT NULL AUTO_INCREMENT,
    `partition_key` INT        NOT NULL,
    `amt`           DECIMAL(5) NULL,
    PRIMARY KEY (`id`, `partition_key`)
) PARTITION BY RANGE (partition_key) PARTITIONS 5
    (
        PARTITION part0 VALUES LESS THAN (201901),
        PARTITION part1 VALUES LESS THAN (201902),
        PARTITION part2 VALUES LESS THAN (201903),
        PARTITION part3 VALUES LESS THAN (201904),
        PARTITION part4 VALUES LESS THAN (201905)
    );

这时候我们先插入一些数据

INSERT INTO `Order` (`id`, `partition_key`, `amt`) VALUES ('1', '201901', '1000');
INSERT INTO `Order` (`id`, `partition_key`, `amt`) VALUES ('2', '201902', '800');
INSERT INTO `Order` (`id`, `partition_key`, `amt`) VALUES ('3', '201903', '1200');

现在我们查询一下，通过 EXPLAIN PARTITION 命令发现SQL优化器只需搜对应的区，不会搜索所有分区

EXPLAIN PARTITIONs select * from `Order` where partition_key = '201901';

如果 sql 语句有问题，那么会走所有区。会很危险。所以分区表后，select 语句 必须走分区键。

EXPLAIN PARTITIONs select * from `Order` where amt > 500;

以下3种不是太常用，就一笔带过了。

LIST 分区

LIST 分区和 RANGE 分区很相似，只是分区列的值是离散的，不是连续的。LIST分区使用VALUES IN，因为每个分区的值是离散的，

HASH 分区

说到哈希，那么目的很明显了，将数据均匀的分布到预先定义的各个分区中，保证每个分区的数量大致相同。

KEY 分区

KEY 分区和 HASH 分区相似，不同之处在于 HASH 分区使用用户定义的函数进行分区，KEY 分区使用数据库提供的函数进行分区。

分区表的原理

分区表是由多个相关的底层表实现，这些底层表也是由句柄对象表示，所以我们也可以直接访问各个分区，存储引擎管理分区的各个底层表和管理普通表一样（所有的底层表都必须使用相同的存储引擎）

分区表的索引只是在各个底层表上各自加上一个相同的索引，从存储引擎的角度来看，底层表和一个普通表没有任何不同，存储引擎也无须知道这是一个普通表还是一个分区表的一部分。

在分区表上的操作按照下面的操作逻辑进行：

select 查询：当查询一个分区表的时候，分区层先打开并锁住所有的底层表，优化器判断是否可以过滤部分分区，然后再调用对应的存储引擎接口访问各个分区的数据。

insert 操作：当写入一条记录时，分区层打开并锁住所有的底层表，然后确定哪个分区接受这条记录，再将记录写入对应的底层表。

delete 操作：当删除一条记录时，分区层先打开并锁住所有的底层表，然后确定数据对应的分区，最后对相应底层表进行删除操作。

update 操作：当更新一条数据时，分区层先打开并锁住所有的底层表，mysql 先确定需要更新的记录在哪个分区，然后取出数据并更新，再判断更新后的数据应该放在哪个分区，然后对底层表进行写入操作，并对原数据所在的底层表进行删除操作

虽然每个操作都会打开并锁住所有的底层表，但这 并不是说分区表在处理过程中是锁住全表的，存储引擎能够自己实现行级锁，如：innodb，则会在分区层释放对应的表锁，这个加锁和解锁过程与普通 Innodb 上的查询类似。

分区和性能 ⭐

一项技术，不是用了就一定带来益处。

比如显式锁功能比内置锁强大，你没玩好可能导致很不好的情况。

分区也是一样，不是启动了分区数据库就会运行的更快，分区可能会给某些 sql 语句性能提高，但是分区主要用于数据库高可用性的管理。

数据库应用分为 2类，一类是 OLTP（在线事务处理），一类是 OLAP（在线分析处理）。

对于 OLAP 应用分区的确可以很好的提高查询性能，因为一般分析都需要返回大量的数据，如果按时间分区，比如一个月用户行为等数据，则只需扫描响应的分区即可。

在 OLTP 应用中，分区更加要小心，通常不会获取一张大表的 10% 的数据，大部分是通过索引返回几条数据即可。

比如一张表 1000w 数据量，如果一句 select 语句走辅助索引，但是没有走分区键。那么结果会很尴尬。如果 1000w 的 B+ 树的高度是 3，现在有 10 个分区。那么不是要 (3 + 3) * 10 次的逻辑 IO？（3 次聚集索引，3 次辅助索引，10 个分区）。所以在 OLTP 应用中请小心使用分区表。

在日常开发中，如果想查看 sql 语句的分区查询结果可以使用 explain partitions + select sql 来获取，partitions 标识走了哪几个分区。

mysql> explain partitions select * from TxnList where startTime>'2016-08-25 00:00:00' and startTime<'2016-08-25 23:59:00';  
+----+-------------+-------------------+------------+------+---------------+------+---------+------+-------+-------------+  
| id | select_type | table             | partitions | type | possible_keys | key  | key_len | ref  | rows  | Extra       |  
+----+-------------+-------------------+------------+------+---------------+------+---------+------+-------+-------------+  
|  1 | SIMPLE      | ClientActionTrack | p20160825  | ALL  | NULL          | NULL | NULL    | NULL | 33868 | Using where |  
+----+-------------+-------------------+------------+------+---------------+------+---------+------+-------+-------------+  
1 row in set (0.00 sec)

总结：分区表为什么不常用 ⭐

在我们项目开发中，分区表其实是很少用的，下面简单说明下几点原因：

分区字段的选择有限制。
若查询不走分区键，则可能会扫描所有分区，效率不会提升。
若数据分布不均，分区大小差别较大，可能性能提升也有限。
普通表改造成分区表比较繁琐。
需要持续对分区进行维护，比如到了6月份前就要新增6月份的分区。
增加学习成本，存在未知风险。

视图是什么？​

视图的作用​

定义视图​

分区表 ⭐​

分区操作示例​

分区类型​

RANGE 分区​

LIST 分区​

HASH 分区​

KEY 分区​

分区表的原理​

分区和性能 ⭐​

总结：分区表为什么不常用 ⭐​